using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Xml;

namespace DbBuilder
{
	public class StaticDataStep
	{
		private readonly string tableName;
		private readonly string fileName;
		private readonly bool redeployable;
		private readonly string dbName;
		private readonly List<string> primaryKey = new List<string>();

		public StaticDataStep(XmlNode stepNode)
		{
			tableName = stepNode.Attributes["TableName"].Value;
			fileName = Path.Combine(Program.Instance.config_root_folder, stepNode.Attributes["FileName"].Value);
			redeployable = stepNode.Attributes["RedeployableCodeObject"] != null && int.Parse(stepNode.Attributes["RedeployableCodeObject"].Value) == 1;
			dbName = Program.Instance.databaseNameMapping[stepNode.Attributes["DBName"].Value];
			foreach (XmlNode node in stepNode.SelectNodes("Key"))
			{
				primaryKey.Add(node.InnerText);
			}
		}

		public bool Execute(bool redeployableOnly)
		{
			if (!redeployable && redeployableOnly)
			{
				return true;
			}

			//Insert/Update
			Logger.WriteTrace("Updating static data in table {0} from file {1}", tableName, fileName);
			CsvTable table = new CsvTable(fileName);

			foreach (string key in primaryKey)
			{
				if (!table.Header.Contains(key))
				{
					throw new InvalidOperationException("CSV file doesn't contain key item");
				}
			}

			StringBuilder updateClause = new StringBuilder();
			StringBuilder whereClause = new StringBuilder();
			StringBuilder fieldList = new StringBuilder();
			StringBuilder valuesList = new StringBuilder();
			StringBuilder keyList = new StringBuilder();

			foreach (string field in table.Header)
			{
				if (primaryKey.Contains(field))
				{
					whereClause.AppendFormat("[{0}] = @{0} and ", field);
					keyList.AppendFormat("{0}, ", field);
				}
				else
				{
					updateClause.AppendFormat("[{0}] = @{0}, ", field);
				}
				fieldList.AppendFormat("[{0}], ", field);
				valuesList.AppendFormat("@{0}, ", field);
			}
			whereClause.StripEnd(5);
			updateClause.StripEnd(2);
			fieldList.StripEnd(2);
			valuesList.StripEnd(2);
			keyList.StripEnd(2);

			string sql = string.Format
				("if exists (select * from [{0}] where {1}) update [{0}] set {2} where {1} else insert into [{0}] ({3}) values ({4}) ",
					tableName, whereClause, updateClause, fieldList, valuesList);

			using (SqlConnection objConnection = new SqlConnection
				(string.Format("Server={0};Database={1};Integrated Security=SSPI;", Program.Instance.SQLServerInstanceName, dbName)))
			{
				objConnection.Open();

				SqlCommand command = new SqlCommand(sql, objConnection);
				foreach (List<string> row in table)
				{
					command.Parameters.Clear();
					for (int i = 0; i < table.Header.Count;i++)
					{
						command.Parameters.AddWithValue(table.Header[i], row[i] != "NULL" ? (object)row[i] : DBNull.Value);
					}
					command.ExecuteNonQuery();
				}
			}
			Logger.WriteTrace("Updated {0} rows", table.Count);
			//TODO: Finout what to do with deletion of records

			return true;
		}
	}
}